import sqlite3
from contextlib import closing

DB_PATH = 'attendance.db'

def init_jielong_db():
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('''
        CREATE TABLE IF NOT EXISTS jielong_task (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id TEXT NOT NULL,
            raw_text TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            debug_log TEXT,
            report_json TEXT,
            output_txt TEXT
        )
        ''')
        conn.commit()

def insert_jielong_task(session_id, raw_text, debug_log=None, report_json=None, output_txt=None):
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('''
            INSERT INTO jielong_task (session_id, raw_text, debug_log, report_json, output_txt)
            VALUES (?, ?, ?, ?, ?)
        ''', (session_id, raw_text, debug_log, report_json, output_txt))
        conn.commit()

def update_jielong_debug_log(session_id, debug_log=None, report_json=None, output_txt=None):
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        sets = []
        params = []
        if debug_log is not None:
            sets.append('debug_log=?')
            params.append(debug_log)
        if report_json is not None:
            sets.append('report_json=?')
            params.append(report_json)
        if output_txt is not None:
            sets.append('output_txt=?')
            params.append(output_txt)
        if sets:
            sql = f"UPDATE jielong_task SET {', '.join(sets)} WHERE session_id=?"
            params.append(session_id)
            c.execute(sql, params)
            conn.commit()

def get_all_jielong_tasks():
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('SELECT session_id, raw_text, created_at, debug_log, report_json, output_txt FROM jielong_task ORDER BY created_at DESC')
        return c.fetchall()

def get_jielong_task(session_id):
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('SELECT session_id, raw_text, created_at, debug_log, report_json, output_txt FROM jielong_task WHERE session_id=?', (session_id,))
        return c.fetchone()
